﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageWorkRecord_SearchWorkRecord

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal Region As String, ByVal Region2 As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal annual As String, ByVal workItem As String, ByVal planID As String, ByVal planItemType As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT A.VolunteerID,C.VolunteerName,B.Text as WorkTypeText,A.PlanName,A.WorkHour,A.WorkType ")
        sql.AppendFormat(", A.WorkStartDate, A.WorkEndDate ")
        sql.AppendFormat(", v.TownName, v.RegionName, LeaderName = v_leader.VolunteerName, ov.OrganizationName ")
        sql.AppendFormat("FROM  ")
        sql.AppendFormat("( ")
        sql.AppendFormat("SELECT A.VolunteerID,A.WorkType,A.WorkHour,E.PlanName,year(C.WorkStartDate) as Annual ")
        sql.AppendFormat(", C.WorkStartDate, C.WorkEndDate, C.WorkHeaderID, E.ItemType, A.Active ")
        sql.AppendFormat("FROM WorkHourRecord A ")
        sql.AppendFormat("inner join WorkRecord B on a.RecordID=B.RecordID and A.WorkType='CleanPlan' and A.Active='Y' ")
        sql.AppendFormat("inner join WorkHeader C on B.WorkHeaderID=C.WorkHeaderID ")
        sql.AppendFormat("inner join CleanPlanMapping D on C.WorkHeaderID=D.WorkHeaderID ")
        sql.AppendFormat("inner join dbo.CleanPlan E on D.PlanID=E.PlanID ")

        sql.AppendFormat("where 1=1 ")
        If planID <> "" Then
            sql.AppendFormat("and E.PlanID = @PlanID ")
            sql.AddWithValue("PlanID", planID)
        End If

        sql.AppendFormat("UNION ALL ")
        sql.AppendFormat("SELECT A.VolunteerID,A.WorkType,A.WorkHour,E.TownName+E.RegionName+D.Location,year(C.WorkStartDate) as Annual ")
        sql.AppendFormat(", C.WorkStartDate, C.WorkEndDate, C.WorkHeaderID, G.ItemType, A.Active ")
        sql.AppendFormat("FROM WorkHourRecord A ")
        sql.AppendFormat("inner join WorkRecord B on A.RecordID=B.RecordID and A.WorkType='FindDirtyHeader' and A.Active='Y' ")
        sql.AppendFormat("inner join WorkHeader C on B.WorkHeaderID=C.WorkHeaderID ")
        sql.AppendFormat("inner join FindDirtyHeader D on C.WorkHeaderID=D.WorkHeaderID ")
        sql.AppendFormat("inner join Village E on D.VillageID=E.RegionID ")
        sql.AppendFormat(" inner join CleanPlanMapping F on C.WorkHeaderID=F.WorkHeaderID ")
        sql.AppendFormat(" inner join dbo.CleanPlan G on F.PlanID=G.PlanID ")
        sql.AppendFormat("UNION ALL ")
        sql.AppendFormat("SELECT A.VolunteerID,A.WorkType,A.WorkHour,C.TownName+C.RegionName+B.Location,year(B.ReportDate) as Annual ")
        sql.AppendFormat(", wh.WorkStartDate, wh.WorkEndDate, wh.WorkHeaderID, E.Itemtype, A.Active ")
        sql.AppendFormat("FROM WorkHourRecord A ")
        sql.AppendFormat("inner join FindDirtyHeader B on A.RecordID=B.FindDirtyHeaderID and A.WorkType='FindDirtyAward' and A.Active='Y' ")
        sql.AppendFormat("inner join Village C on B.VillageID=C.RegionID ")
        sql.AppendFormat("inner join WorkHeader wh on B.WorkHeaderID=wh.WorkHeaderID ")
        sql.AppendFormat(" inner join CleanPlanMapping D on D.WorkHeaderID=wh.WorkHeaderID ")
        sql.AppendFormat(" inner join dbo.CleanPlan E on E.PlanID=D.PlanID ")
        sql.AppendFormat(") A ")
        sql.AppendFormat("LEFT JOIN CodeMapping B on A.WorkType=B.Value and B.TableName='WorkHourRecord' and B.ColumnName='WorkType' ")
        sql.AppendFormat("inner JOIN Volunteer C on A.VolunteerID = C.VolunteerID ")
        sql.AppendFormat("inner JOIN Village v on C.VillageID = v.RegionID ")
        sql.AppendFormat("inner JOIN WorkHeader wh on A.WorkHeaderID = wh.WorkHeaderID ")
        sql.AppendFormat("inner JOIN Volunteer v_leader on wh.LeaderID = v_leader.VolunteerID ")
        sql.AppendFormat("inner JOIN OrganizationView ov on wh.UOrganizationID = ov.UOrganizationID ")
        sql.AppendFormat("WHERE C.Active ='Y' and A.Active = 'Y' ")
        sql.AppendFormat("AND (C.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0} or '{0}' =0 )) ", VOrganizationID)
        sql.AppendFormat("AND (C.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        sql.AppendFormat("AND (C.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (C.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (C.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        sql.AppendFormat("AND ((C.MobilePhone like '%{0}%' or '{0}'='') or (C.OfficePhone like '%{0}%' or '{0}'='')) ", TEL)

        If annual <> "" Then
            sql.AppendFormat("AND Annual = '{0}' ", annual)
        End If

        If workItem <> "" Then
            sql.AppendFormat("and A.WorkType = @WorkItem ")
            sql.AddWithValue("WorkItem", workItem)
        End If

        If planItemType <> "" Then
            sql.AppendFormat(" and A.ItemType = @ItemType ")
            sql.AddWithValue("ItemType", planItemType)
        End If

        sql.AppendFormat(" ORDER BY A.VolunteerID,A.WorkType ")


        Return dat.GetData(sql)

    End Function

  
End Class
